﻿using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.Entity.View;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_WarehousingNoticeBusiness : BaseBusiness<PB_WarehousingNotice>, IPB_WarehousingNoticeBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        public PB_WarehousingNoticeBusiness(IDbAccessor db, IOperator @operator, IServiceProvider svcProvider)
            : base(db)
        {
            _operator = @operator;
        }
        readonly IServiceProvider _ServiceProvider;
        #region 外部接口

        public async Task<PageResult<PB_WarehousingNotice>> GetDataListAsync(PageInput<ConditionDTO> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_WarehousingNotice>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_WarehousingNotice, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            q = q.Where(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        } 
        public async Task<PB_WarehousingNotice> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(PB_WarehousingNotice data)
        {
            data.QrCode = "21," + data.Id;
            data.DeptId = _operator.Property.DepartmentId;
            data.CreatorName = _operator?.Property?.RealName;
            data.CreateTime = DateTime.Now;
            data.CreatorId = _operator.UserId;
            data.ReserveState = 0;
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(PB_WarehousingNotice data)
        {
            await UpdateAsync(data);
        }


        [Transactional]
        public async Task DeleteDataAsync(List<string> ids)
        {
            string sqlMain = string.Empty;
            //查询备货单明细信息
            List<PB_PickUpList> PickUpList = await Db.GetIQueryable<PB_PickUpList>().Where(x => ids.Contains(x.ParentId)).ToListAsync();
            var picknums = PickUpList.Where(x => x.ReadyNums > 0).ToList();
            if (picknums.Count > 0)
            {
                throw new BusException("删除的备货单中有已备货的不能删除");
            }
            else
            {
                #region 清理占用数量
                //查询库存汇总信息
                List<IT_LocalMaterial> LocalMaterialList = await Db.GetIQueryable<IT_LocalMaterial>().ToListAsync();
                foreach (var item in PickUpList)
                {
                    var itemdata = LocalMaterialList.Where(x => x.LocalId == item.LocationId && x.MaterialId == item.MaterialId).FirstOrDefault();
                    if (itemdata != null)
                    {
                        sqlMain += @" update IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)-" + item.StockVolume + " where Id='" + itemdata.Id + "'; ";
                    }
                }

                #endregion
                List<PB_PickUpMain> PickUpMain = await Db.GetIQueryable<PB_PickUpMain>().Where(x => ids.Contains(x.Id) && x.AssociatedDelivery != null).ToListAsync();
                var AssociatedDeliverydata = PickUpMain.GroupBy(x => new { x.AssociatedDelivery }).Select(x => new { AssociatedDelivery = x.Key.AssociatedDelivery }).ToList();
                foreach (var item in AssociatedDeliverydata)
                {
                    sqlMain += @" Update PB_DeliveryPlanDetails  set States=2  WHERE Id='" + item + "'; ";
                }
                sqlMain += @" DELETE FROM PB_PickUpList WHERE ParentId in ('" + string.Join("','", ids.ToArray()) + "'); ";
                sqlMain += @" DELETE FROM PB_PickUpMain WHERE Id in ('" + string.Join("','", ids.ToArray()) + "'); ";
                await ExecuteSqlAsync(sqlMain);
            }

         
                var pickuplist =await Db.GetIQueryable<PB_PickListSteps>( ).Where(m=>ids.Contains(m.ParentId)).ToListAsync();
                if (pickuplist.Count > 0)
                {

                    foreach (var items in pickuplist)
                    {
                        string stepindex = items.StepIndex + "Ready";
                        string sqlupdate = "update  PB_DesignDetail set " + stepindex + "= IFNULL(" + stepindex + ", 0)-" + items.StockVolume + " where MaterialId='" + items.MaterialId + "' AND Id='" + items.SubId + "'";
                        await Db.ExecuteSqlAsync(sqlupdate);
                    }
                    await ExecuteSqlAsync("DELETE FROM PB_PickListSteps WHERE ParentId in (" + String.Join(", ", ids).Replace("\"", "'") + ")");
                }
            
           
        }

        public async Task<PageResult<PB_WarehousingNoticeDTO>> GetDataListAsyncDTO(PageInput<ConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_WarehousingNotice, Base_Department, Base_User, PB_PackList, PB_Material, PB_WarehousingNoticeDTO>> select = (a, b, c, d, e) => new PB_WarehousingNoticeDTO
            {
                DeptIdName = b.Name,
                UserName = c.UserName,
                MealeName = e.Name,
                Nnums = d.Nums,
                Fu = a.Id,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.CreatorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_PackList>() on a.Id equals d.NoticeId into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Material>() on d.MaterialId equals e.Id into de
                    from e in de.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e);
            q = q.Where(x =>
                    x.Type == 1);
            var where = LinqHelper.True<PB_WarehousingNoticeDTO>();
            if (!input.Search.Condition.IsNullOrEmpty() && !input.Search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_WarehousingNoticeDTO, bool>(
                  ParsingConfig.Default, false, $@"{input.Search.Condition}.Equals(@0)", input.Search.Keyword);
                where = where.And(newWhere);
            }
            where = where.And(x => x.Type == 1);
            where = where.And(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PageResult<PB_WarehousingNoticeDTO>> GetdeliverGoods(PageInput<ConditionDTO> input)
        {
            var search = input.Search;
            Expression<Func<PB_WarehousingNotice, Base_Department, Base_User, PB_WarehousingNoticeDTO>> select = (a, b, c) => new PB_WarehousingNoticeDTO
            {
                DeptIdName = b.Name,
                UserName = c.UserName,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.CreatorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    select @select.Invoke(a, b, c);
            q = q.Where(x =>
                    x.Type == 3);
            var where = LinqHelper.True<PB_WarehousingNoticeDTO>();

            if (!input.Search.Condition.IsNullOrEmpty() && !input.Search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<PB_WarehousingNoticeDTO, bool>(
                  ParsingConfig.Default, false, $@"{input.Search.Condition}.Equals(@0)", input.Search.Keyword);
                where = where.And(newWhere);
            }
            where = where.And(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PageResult<PB_WarehousingNoticeDTO>> GetStockUp(PageInput<List<ConditionDTO>> input)
        {
            var search = input.Search;
            Expression<Func<PB_WarehousingNotice, Base_Department, Base_User,PB_Customer, PB_WarehousingNoticeDTO>> select = (a, b, c,d) => new PB_WarehousingNoticeDTO
            {
                DeptIdName = b.Name,
                UserName = c.UserName,
                PlannedQuantity = 0,
                ShipmentQuantity = 0,
                tyeInfo = a.Type,
                CustomerName=d.Name,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<Base_Department>() on a.DeptId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<Base_User>() on a.CreatorId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Customer>() on a.CusID equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    select @select.Invoke(a, b, c,d);
            q = q.Where(x =>
                    x.tyeInfo == 4);
            var where = LinqHelper.True<PB_WarehousingNoticeDTO>();
            if (input.Search.Count() > 0)
            {
                foreach (var item in input.Search)
                {
                    if (!item.Condition.IsNullOrEmpty() && !item.Keyword.IsNullOrEmpty())
                    {
                        var newWhere = DynamicExpressionParser.ParseLambda<PB_WarehousingNoticeDTO, bool>(
                          ParsingConfig.Default, false, $@"{item.Condition}.Equals(@0)", item.Keyword);
                        where = where.And(newWhere);
                    }

                }
            }

            //where = where.And(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        }

        /// <summary>
        /// 入库通知单审核
        /// </summary>
        /// 生成收货单增加UpdateTime字段,审核通过把包对应的成品关联收货单   2022-03-02  赵锦涛
        /// <returns></returns>
        [Transactional]
        public async Task<AjaxResult> SaveToExamine(string idIn, bool judge)
        {
            Base_User userdata = await Db.GetIQueryable<Base_User>().Where(x => x.Id == _operator.UserId).FirstOrDefaultAsync();
            int Return = 0;
            if (judge)
            {
                TimeSpan ts = DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0);
                string sjtime = Convert.ToInt64(ts.TotalSeconds).ToString();
                string id = IdHelper.GetId();
                string qrcode = h_TdRe + "," + id;
                string sqlstr = "UPDATE  PB_WarehousingNotice set CustomerToexamine=1  where Id='" + idIn + "';";
                sqlstr += " insert into TD_Receiving (Id,StorId,Code,OrderTime,RecTime,Type,RefCode,Status,TotalNum,InNum,TotalAmt,Remarks,CreateTime,CreatorId,Deleted,QrCode,CusID,ConfirmUserId,ConfirmTime,AuditUserId,AuditeTime,DeptId,UpdateTime)select '" + id + "',IFNULL(StorId,'1468472989174468608'),'" + sjtime + "',NOW(),NOW(),'Product',id,3,IFNULL((SELECT sum(Nums) from PB_PackList  where NoticeId='" + idIn + "'),0),0,0,Memo,NOW(),'" + _operator.UserId + "',0,'" + qrcode + "',CusID,'" + _operator.UserId + "',NOW(),'" + _operator.UserId + "',NOW()," + userdata.DepartmentId + ",NOW() from  PB_WarehousingNotice where id='" + idIn + "';";
                sqlstr += "INSERT into TD_RecDetail (Id ,RecId ,StorId  ,MaterialId  ,PlanNum ,RecNum ,InNum ,CreateTime ,CreatorId ,Deleted,price,amount,TaskId) SELECT UUID_SHORT(),'" + id + "',(select StorId from PB_WarehousingNotice where id='" + idIn + "' ),MaterialId,IFNULL(Nums,0),IFNULL(Nums,0),0,NOW(),'" + _operator.UserId + "',0,0,0,TaskId from ( select MaterialId,TaskId,SUM(Nums) AS Nums from (SELECT MaterialId,TaskId,SUM(Nums) AS Nums FROM (  SELECT * FROM PB_PackList WHERE NoticeId='" + idIn + "' and PackageType = 0	)AS T GROUP BY MaterialId,TaskId union  SELECT c.MaterialId,a.TaskId,Count(c.MaterialId) as Nums FROM PB_PackList as a left join PB_PackListInfo as b on a.Id = b.PackId left join PB_JobBookingQrCode as c on b.JobBookingId = c.Id WHERE a.NoticeId = '" + idIn + "' and a.PackageType = 1 group by c.MaterialId,a.TaskId,a.Nums )AS t group by MaterialId,TaskId  ) as tt;";
                Return = await ExecuteSqlAsync(sqlstr);

                //获取包对应的成品码
                Expression<Func<PB_PackListInfo, PB_PackList, PackInfoJobBookingDTO>> select = (a, b) => new PackInfoJobBookingDTO
                {
                    JobBookingId = a.JobBookingId,
                    NoticeId = b.NoticeId
                };
                select = select.BuildExtendSelectExpre();
                var q = from a in Db.GetIQueryable<PB_PackListInfo>().AsExpandable()
                        join b in Db.GetIQueryable<PB_PackList>() on a.PackId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        select @select.Invoke(a, b);
                var listdata = await q.Where(x => x.NoticeId == idIn).ToListAsync();
                var list = listdata.GroupBy(x => new { x.JobBookingId }).Select(x => new { JobBookingId = x.Key.JobBookingId }).ToList();
                string jobid = string.Empty;
                foreach (var item in list)
                {
                    jobid += "'" + item.JobBookingId + "',";
                }
                jobid = jobid.Substring(0, jobid.Length - 1);
                //成品码关联收货单id
                string sqlMain = @"update PB_JobBookingQrCode set RecId='" + id + "' where Id in (" + jobid + ")";
                await ExecuteSqlAsync(sqlMain);
            }
            else
            {
                Return = await ExecuteSqlAsync("UPDATE PB_WarehousingNotice  set CustomerToexamine=2 where Id='" + idIn + "' ");
            }
            if (Return > 0)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "请求成功！"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "提交数据有误！"
                };

                return res;
            }
        }

        public async Task<GetStockDetailsDTO> GetStockDetails(string id)
        {
            GetStockDetailsDTO getStockDetailsDTO = new GetStockDetailsDTO();
            getStockDetailsDTO.Data = Db.GetListBySql<PB_PickUpMainData>("SELECT *  from PB_PickUpMain WHERE Id='" + id + "'").FirstOrDefault();
            var dbInfo = Db.GetListBySql<DistributionDataDTO>(@"SELECT     c.Id DesignListId,d.Id PB_DesignListInfo,a.Id BatchNumber,d.Nums,d.RealNums,b.MaterialId,b.ReadyNums,b.StockVolume,g.*  FROM PB_WarehousingNotice a 
INNER JOIN PB_StockUp b on a.Id = b.OddNumber
INNER JOIN PB_DesignDetail d on b.SubId = d.Id
INNER JOIN PB_DesignList c on d.ParentId = c.Id

INNER JOIN(
    SELECT a.StockNumber, b.Id MaterialId, b.`Name` MaterialName, c.`Name` LocationName, d.`Name` StorageName, e.`Name` StorAreaName, b.Company   FROM PB_Stock a
INNER JOIN
(
    SELECT a.PackId, d.*, h.`Name` Company FROM PB_PackListInfo a INNER JOIN
PB_JobBookingQrCode b on a.JobBookingId = b.Id
INNER JOIN PB_ProductionOrderDetail c on b.OrderDetailId = c.Id
INNER JOIN PB_Material d on c.MaterialId = d.Id
LEFT JOIN Base_UnitGroup h on d.UnitGroupId = h.Id
) b
on a.PackId = b.PackId
LEFT JOIN PB_Location c on a.LocationId = c.id
LEFT JOIN PB_Storage d on c.StorId = d.id
LEFT JOIN PB_StorArea e on c.AreaId = e.id
) g on b.MaterialId = g.MaterialId WHERE   a.Id='" + id + "' ");
            getStockDetailsDTO.Sub = dbInfo;
            return getStockDetailsDTO;
        }

        public async Task UpdateReserveState(string id)
        {
          
Db.ExecuteSql("UPDATE  PB_PickUpMain set Status=2,UpdateTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "'  where Id='" + id + "' ");
        }

        ///// <summary>
        ///// 备货单数据
        ///// </summary>
        ///// <param name="ParentId"></param>
        ///// <returns></returns>
        //public async Task<List<PB_PickUpMaininfo>> GetDataListInfo(string ParentId)
        //{
        //    Expression<Func<PB_PickUpMaininfo,PB_Customer, PB_PickUpMaininfo>> select = (a, b) => new PB_PickUpMaininfo
        //    {
               
        //        CustomerName=b.Name,
        //        CustomerPhone=d.Phone,
        //    };
        //    select = select.BuildExtendSelectExpre();
        //    var q = from a in GetIQueryable().AsExpandable()
        //            join b in Db.GetIQueryable<Base_Department>() on a.StorId equals b.Id into ab
        //            from b in ab.DefaultIfEmpty()
        //              join d in Db.GetIQueryable<PB_Customer>() on a.CusID equals d.Id into ad
        //            from d in ad.DefaultIfEmpty()
        //            select @select.Invoke(a, b,d);
        //    List<PB_PickUpMaininfo> listdata = new List<PB_PickUpMaininfo>();
        //    var qdata = await q.Where(x => x.Id==ParentId).ToListAsync();
        //    foreach (var item in qdata)
        //    {
        //        Expression<Func<PB_PickUpList, PB_Material , PB_StockUpDTO>> selects = (a, b) => new PB_StockUpDTO
        //        {
        //            MaterialName=b.Name ,
        //            MaterialNameQrcode=b.QrCode,
        //            Spec=b.Spec,
        //            Ofmaterial=b.Ofmaterial,
        //            MaterialCode=b.Code,

        //        };
        //        selects = selects.BuildExtendSelectExpre();
        //        var k = from a in Db.GetIQueryable<PB_PickUpList>().AsExpandable()
        //                join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
        //                from b in ab.DefaultIfEmpty()
        //                select @selects.Invoke(a, b);
        //        k= k.Where(x => x.OddNumber == item.Id);
        //        var res = await k.ToListAsync();
        //        PB_PickUpMaininfo pblist = new PB_PickUpMaininfo();
        //        pblist.Id = item.Id;
        //        pblist.QrCode = item.QrCode;
        //        pblist.DeptIdName = item.DeptIdName;
        //        pblist.CreatorId = item.CreatorId;
        //        pblist.CustomerName = item.CustomerName;
        //        pblist.CreateTime = item.CreateTime;
        //        pblist.Deleted = item.Deleted;
        //        pblist.ParentId = item.ParentId;
        //        pblist.CustomerName = item.CustomerName;
        //        pblist.Memo = item.Memo;
        //        pblist.CusID = item.CusID;
        //        pblist.CustomerAddress = item.CustomerAddress;
        //        pblist.CustomerPhone = item.CustomerPhone;
        //        pblist.Subdata = res;
        //        listdata.Add(pblist);
        //    }
        //    if (listdata.Count > 0)
        //    {
        //        return listdata;
        //    }
        //    else
        //    {
        //        throw new BusException("暂无数据");
        //    }
        //    throw new NotImplementedException();
        //}

  
        public  async Task<AjaxResult> SaveToExamineedit(string idIn, bool judge)
        {
            int Return = 0;
            if (judge)
            {
                Return =await ExecuteSqlAsync(  "UPDATE  PB_WarehousingNotice set CustomerToexamine=1  where Id='" + idIn + "';");
            }
            else
            {
                Return = await ExecuteSqlAsync("UPDATE PB_WarehousingNotice  set CustomerToexamine=2 where Id='" + idIn + "' ");
            }
            if (Return > 0)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "请求成功！"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "提交数据有误！"
                };

                return res;
            }
        }

        public async Task<GetStockDetailsDTO> GetPBPickUpList(string id)
        {
            GetStockDetailsDTO getStockDetailsDTO = new GetStockDetailsDTO();
            getStockDetailsDTO.Data = Db.GetListBySql<PB_PickUpMainData>("select * from ( SELECT t.* FROM (SELECT  a.*,P.NAME AS ProjectName,I.BuildingNo AS BuildingNodetal,GROUP_CONCAT(P.`NAME`, I.BuildingNo, K.TrainNumber SEPARATOR ',') AS DeliveryPlanName FROM  PB_PickUpMain a    INNER JOIN PB_DeliveryNoticeItem I ON a.ParentId = I.ID  INNER JOIN PB_DeliveryNotice V ON I.ParentId = V.Id  INNER JOIN PB_Project P ON V.ProjectId = P.Id  LEFT JOIN PB_DeliveryPlanDetails AS K ON a.AssociatedDelivery = K.Id  AND K.ProjectId = P.Id WHERE a.Id = '" + id+ "') AS t ) AS ok LEFT JOIN(SELECT ParentId, IFNULL(SUM(SS),0) AS Totalweight FROM(SELECT list.ParentId,IFNULL(SUM(list.StockVolume) * mater.Weight, 0) AS SS FROM  PB_PickUpList AS list LEFT JOIN PB_Material AS mater ON list.MaterialId = mater.Id  WHERE list.ParentId = '" + id+ "'  GROUP BY   list.MaterialId   ) AS o    GROUP BY ParentId	) AS T ON T.ParentId = ok.Id").FirstOrDefault();
            string sqlMain = @"select 
a.ParentId BatchNumber,
	a.AreaId,
	a.RackId,
	a.LocationId,
	a.MaterialId,
	a.SubId AS PB_DesignListInfo,
	a.Remarks AS Remarks,
	b.`Code` MaterialCode,
	b.`Name` MaterialName,
	b.Spec AS MaterialSpec,
	a.Id as Id,
	c.`Name` LocationName,
	d.StorAreaName,
	d.`Name` StorageName,
	f.`Name` RackName,
	(IFNULL(lm.ScanNum,0)-IFNULL(lm.Nums_R,0)) as StockNumber,
	a.StockVolume,
	a.StockVolume StockVolumecache,
	a.ReadyNums,
	concat( a.SubId, '_', a.LocationId ) AS `onlyId`,
	IFNULL(tb.RealNums,0) as RealNums,
(IFNULL(a.ReadyNums,0)*IFNULL(b.Weight,0)) as PreparedWeight,
  case  when (IFNULL( tb.RealNums, 0 )-IFNULL(tbp.ReadyNums,0))<0 then 0 else (IFNULL( tb.RealNums, 0 )-IFNULL(tbp.ReadyNums,0)) end as ShouldNumber,
items.`Name` groupName
 from PB_PickUpList as a
 left join PB_PickUpMain as p on a.ParentId=p.Id
 left join PB_Material as b on a.MaterialId=b.Id
LEFT JOIN PB_Location c ON a.LocationId = c.Id
left join Base_UnitItems AS items on b.storageId=items.Id
LEFT JOIN ( SELECT m.Id, `Name`, m.StorAreaName FROM View_StorAreaRackLocation m LEFT JOIN Base_Department n ON m.StorId = n.id ) d ON a.LocationId = d.id
LEFT JOIN PB_Rack f ON c.RackId = f.id
left join IT_LocalMaterial as lm on a.LocationId=lm.LocalId and a.MaterialId=lm.MaterialId
left join 
(
select DesignId,MaterialId,SUM(RealNums) as RealNums from 
(
select b.DesignId,c.MaterialId,c.RealNums from PB_PickUpMain as a 
left join PB_DeliveryNoticeItem as b on a.ParentId=b.Id
left join PB_DesignDetail as c on b.DesignId=c.ParentId
where a.Id='{0}' and c.State!=2 
UNION ALL 
select b.DesignId,c.MaterialId,-c.RealNums as RealNums from PB_PickUpMain as a 
left join PB_DeliveryNoticeItem as b on a.ParentId=b.Id 
left join PB_DesignDetail as c on b.DesignId=c.ParentId 
where a.Id='{0}' and c.State=2 
) as t group by DesignId,MaterialId
) as tb on a.MaterialId=tb.MaterialId 
left join
(
select a.ParentId,b.MaterialId,SUM(ReadyNums) as ReadyNums from PB_PickUpMain as a 
left join PB_PickUpList as b on a.Id=b.ParentId
where a.ParentId='{1}' and a.Status=3 group by a.ParentId,b.MaterialId
) as tbp on p.ParentId=tbp.ParentId and a.MaterialId=tbp.MaterialId
where a.ParentId='{0}' order by PB_DesignListInfo,LocationId";
            string sql = string.Format(sqlMain, id, getStockDetailsDTO.Data.ParentId);
            var dbInfo = Db.GetListBySql<DistributionDataDTO>(sql);
            List<StepList> stepsLists = new List<StepList>();
            getStockDetailsDTO.Sub = dbInfo;
            StringBuilder StepList = new StringBuilder();
              StepList.AppendLine("SELECT");
            StepList.AppendLine("a.MaterialId,");
            StepList.AppendLine("b.`Name`,");
            StepList.AppendLine("a.LocationId,");
            StepList.AppendLine("IFNULL(sum(CASE WHEN a.StepIndex = 'OneStep' THEN a.StockVolume END), 0) AS OneStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'TwoStep' THEN a.StockVolume END), 0 ) AS TwoStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'ThreeStep' THEN a.StockVolume END), 0 ) AS ThreeStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'FourStep' THEN a.StockVolume END), 0 ) AS FourStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'FiveStep' THEN a.StockVolume END), 0 ) AS FiveStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'SixStep' THEN a.StockVolume END), 0 ) AS SixStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'SevenStep' THEN a.StockVolume END), 0 ) AS SevenStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'EightStep' THEN a.StockVolume END), 0 ) AS EightStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'NineStep' THEN a.StockVolume END), 0 ) AS NineStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'TenStep' THEN a.StockVolume END), 0 ) AS TenStep,");
            StepList.AppendLine("IFNULL( sum(CASE WHEN a.StepIndex = 'FifteenStep' THEN a.StockVolume END), 0 ) AS FifteenStep");
            StepList.AppendLine("FROM");
            StepList.AppendLine("PB_PickListSteps AS a");
            StepList.AppendLine("LEFT JOIN PB_Material AS b ON a.MaterialId = b.Id");
            StepList.AppendLine("WHERE");
            StepList.AppendLine("a.ParentId = '"+id+"'");
            StepList.AppendLine("GROUP BY a.MaterialId,a.LocationId");
            StepList.AppendLine("ORDER BY a.MaterialId,a.LocationId");
            stepsLists = await Db.GetListBySqlAsync<StepList>(StepList.ToString());
            getStockDetailsDTO.StepList = stepsLists;
            var Steps =await Db.GetIQueryable<PB_PickListSteps>().Where(m=>m.ParentId==id).ToListAsync();
            var listd = Steps.GroupBy(x => new { x.StepIndex, }).Select(x => new { StepIndex = x.Key.StepIndex, Nums = x.Sum(n => n.StockVolume) });
           
            List<StepsListKey> stepsListKeys1 = new List<StepsListKey>();
            foreach (var item in listd)
            {
                StepsListKey stepsListKeys = new StepsListKey();
                stepsListKeys.key = item.StepIndex;
                stepsListKeys1.Add(stepsListKeys);
            }
            getStockDetailsDTO.StepsListKey = stepsListKeys1;
            return getStockDetailsDTO;
        }

        public async Task<AjaxResult> SavePBPickUpList(string idIn, bool judge)
        {
          
            int Return = 0;
            if (judge)
            {
             
                Return = await ExecuteSqlAsync("UPDATE  PB_PickUpMain set Status=1 , ReviewerId='"+_operator.Property?.Id+"'  ,  ReviewerName='" + _operator.Property?.RealName + "',ReviewTIme=NOW(),UpdateTime=NOW()   where Id='" + idIn + "';");
            }
            else
            {
                //Return = await ExecuteSqlAsync("UPDATE PB_PickUpMain  set Status=2 where Id='" + idIn + "' ");
            }
            if (Return > 0)
            {
                AjaxResult res = new AjaxResult
                {
                    Success = true,
                    Msg = "请求成功！"
                };

                return res;
            }
            else
            {
                AjaxResult res = new AjaxResult
                {
                    Success = false,
                    Msg = "提交数据有误！"
                };

                return res;
            }
        }

        //public async Task<PageResult<DeliveryNoticeDTO>> GetDeliveryNoticeInfo(string pid) 
        //{
        //    //获取主表数据（关联PB_WarehousingNotice+PB_DesignList）
        //    Expression<Func<PB_WarehousingNotice, PB_DesignList, DeliveryNoticeDTO>> select = (a, b) => new DeliveryNoticeDTO
        //    {
        //        BuildingNumberID =b.BuildingNumberID,
        //        ProjectId = b.ProjectId,
        //        ProjectName = b.ProjectName,
        //        StandsNums = b.StandsNums,
        //        ProjectAddress = b.ProjectAddress,
        //        LiftingMode = b.LiftingMode,
        //        Salesman = b.Salesman,
        //        Phone0 = b.Phone0,
        //        StepHeight = b.StepHeight,
        //        Receiver = b.Receiver,
        //        Phone1 = b.Phone1,
        //        InDate = b.InDate,
        //        OrderDate = b.OrderDate,
        //        Style = b.Style,
        //        PicUrl = b.PicUrl,
        //        Color = b.Color
        //    };
        //    select = select.BuildExtendSelectExpre();
        //    var q = from a in GetIQueryable().AsExpandable()
        //            join b in Db.GetIQueryable<PB_DesignList>() on a.ParentId equals b.Id into ab
        //            from b in ab.DefaultIfEmpty()
        //            select @select.Invoke(a, b);
        //    q = q.Where(x =>x.ParentId.Equals(pid));
        //}

        /// <summary>
        /// PC入库通知列表
        /// </summary>
        /// <param name="input"></param>
        /// 2022-03-03   赵锦涛
        /// <returns></returns>
        public async Task<PageResult<View_WarehousingNotice>> GetWarehousingNoticeListAsync(PageInput<ConditionDTO> input)
        {
            var q = Db.GetIQueryable<View_WarehousingNotice>();
            var where = LinqHelper.True<View_WarehousingNotice>();
            var search = input.Search;

            //筛选
            if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            {
                var newWhere = DynamicExpressionParser.ParseLambda<View_WarehousingNotice, bool>(
                    ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
                where = where.And(newWhere);
            }
            if (!search.ProjectName.IsNullOrEmpty()) {
                var newWhere = DynamicExpressionParser.ParseLambda<View_WarehousingNotice, bool>(
                        ParsingConfig.Default, false, $@"Id.Contains(@0)", search.ProjectName);
                where = where.And(newWhere);
            }
            q = q.Where(m => _operator.Property.DeptIds.Contains(m.DeptId));
            return await q.Where(where).GetPageResultAsync(input);
        }

        /// <summary>
        /// PC查看入库通知单明细
        /// </summary>
        /// <param name="NoticeId">入库通知单Id</param>
        /// 2022-03-03   赵锦涛
        /// 2022-03-24   关联物料inner join 修改为left join   赵锦涛
        /// <returns></returns>
        public async Task<List<PB_WarehousingNoticeDetailed>> GetWarehousingNoticeDetailedAsync(string NoticeId)
        {
            string sqlMain = @"select *,ROW_NUMBER() over(order by PackId desc) as SerialNumber,M.Spec as Mspec,
	M.Code as Mcode from ( select MaterialId,PackId,BoxName,Nums,PackageType from ( select c.MaterialId,d.PackId,e.BoxName,e.Nums,e.PackageType from PB_WarehousingNotice as a left join TD_Receiving as b on a.Id=b.RefCode left join PB_JobBookingQrCode as c on b.Id=c.RecId left join PB_PackListInfo as d on c.Id=d.JobBookingId left join PB_PackList as e on d.PackId=e.Id where a.Id='" + NoticeId + "' and a.CustomerToexamine=1 ) as t where IFNULL(PackId,'')!='' and PackageType=0 group by MaterialId,PackId,BoxName,Nums,PackageType union all select '' as MaterialId,PackId,BoxName,Nums,PackageType from ( select c.MaterialId,d.PackId,e.BoxName,e.Nums,e.PackageType from PB_WarehousingNotice as a left join TD_Receiving as b on a.Id=b.RefCode left join PB_JobBookingQrCode as c on b.Id=c.RecId left join PB_PackListInfo as d on c.Id=d.JobBookingId left join PB_PackList as e on d.PackId=e.Id where a.Id='" + NoticeId + "' and a.CustomerToexamine=1 ) as t where IFNULL(PackId,'')!='' and PackageType=1 group by PackId,BoxName,Nums,PackageType union all select c.MaterialId,'' as PackId,d.`Name` as BoxName,c.RecNum as Nums,'' as PackageType from PB_WarehousingNotice as a left join TD_Receiving as b on a.Id=b.RefCode left join TD_RecDetail as c on b.Id=c.RecId left join PB_Material as d on c.MaterialId=d.Id where a.Id='" + NoticeId + "' and a.CustomerToexamine=1 and d.IsSingle=1 union ALL select b.MaterialId,b.Id as  PackId,b.BoxName,b.Nums,b.PackageType from PB_WarehousingNotice as a left join PB_PackList as b on a.Id=b.NoticeId where a.Id='" + NoticeId + "' and a.CustomerToexamine=0 )as t LEFT JOIN PB_Material AS M on t.MaterialId=M.Id ";
            return await Db.GetListBySqlAsync<PB_WarehousingNoticeDetailed>(sqlMain);
        }


        #endregion

        #region 私有成员

        #endregion
    }
}